﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FIPSSearch
{
    class DistrictRepository : RepositoryBase
    {

        public DistrictRepository(string connectionString) : base(connectionString) { }

        /// <summary>
        /// Creates the SchoolDistrict table from input file
        /// </summary>
        /// <param name="inputfile">file of school districts</param>
        /// <returns>School District table</returns>
        public List<SchoolDistrict> ParseSchoolDistrictFile(FileInfo inputfile)
        {
            var distlist = new List<SchoolDistrict>();

            using (var file = inputfile.OpenText())
            {
                var headerLine = file.ReadLine();
                while (!file.EndOfStream)
                {
                    var line = file.ReadLine();
                    var fields = line.Split(',');
                    var district = new SchoolDistrict();

                    district.State = fields[0];
                    district.StateCode = fields[1];
                    district.DistrictCode = int.Parse(fields[2]);
                    district.DistrictName = fields[3];
                    district.DistrictType = fields[4];

                    distlist.Add(district);
                }
            }
            return distlist;
        }

        /// <summary>
        /// Clears the School District table using SQL command
        /// </summary>
        public void ClearTable()
        {
            base.ClearTable("dbo.SchoolDistrict");
        }

        /// <summary>
        /// Loads School Districts table using Enitity Framework
        /// </summary>
        /// <param name="source">List of school districts to upload</param>
        public void LoadTable(IList<SchoolDistrict> source)
        {
            var sw = new Stopwatch();
            sw.Start();

            using (var context = new CensusToolsEntities())
            {
                context.Configuration.AutoDetectChangesEnabled = false; //this makes bulk uploads faster. Not exactly sure why.
                foreach (var district in source)
                {
                    context.SchoolDistricts.Add(district);
                }
                context.SaveChanges();
            }

            sw.Stop();
            Console.WriteLine("Total Seconds: " + sw.Elapsed.TotalSeconds);
        }

        /// <summary>
        /// Loads the School District table using SQL Bulk Copy
        /// </summary>
        /// <param name="source">list of school districts to upload</param>
        public void LoadTableFast(IList<SchoolDistrict> source)
        {
            var sw = new Stopwatch();
            sw.Start();

            var table = new DataTable();
            table.Columns.Add("State", typeof(string));
            table.Columns.Add("StateCode", typeof(short));
            table.Columns.Add("DistrictCode", typeof(int));
            table.Columns.Add("DistrictName", typeof(string));
            table.Columns.Add("DistrictType", typeof(string));

            foreach (var item in source)
            {
                var row = table.NewRow();
                row["State"] = item.State;
                row["StateCode"] = item.StateCode;
                row["DistrictCode"] = item.DistrictCode;
                row["DistrictName"] = item.DistrictName;
                row["DistrictType"] = item.DistrictType;
                table.Rows.Add(row);
            }

            UploadDataTable(table, "dbo.SchoolDistrict");
            sw.Stop();
            Console.WriteLine("Using ADO with Bulk Copy took " + sw.Elapsed.TotalSeconds + "seconds");
        }



    }
}